﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Runtime.InteropServices;
using System.Text;
using System.Threading.Tasks;
using NTQOnlineExam.Services.Infrastructure;
using NTQOnlineExam.Services.Models;

namespace NTQOnlineExam.Services.DBCommand.Examination
{
    public class SearchExamsDbCommand : ICommand<SearchResult<ExaminationDTO>>
    {
        private int _userId;
        private int _termId;
        private int _pageIndex;
        private int _pageSize;
        private string _sorting;

        public SearchExamsDbCommand(int userId, int termId, int pageIndex, int pageSize, string sorting)
        {
            _userId = userId;
            _termId = termId;
            _pageIndex = pageIndex;
            _pageSize = pageSize;
            _sorting = sorting;
        }

        public SearchResult<ExaminationDTO> Execute()
        {
            var connectionString = ConfigurationSettings.AppSettings["ConnectionString"];
            var ds = new DataSet();
            using (var con = new SqlConnection(connectionString))
            {
                int totalRecord;
                using (var cmd = new SqlCommand("[SearchExam]", con))
                {
                    var adapt = new SqlDataAdapter(cmd);
                    adapt.SelectCommand.CommandType = CommandType.StoredProcedure;
                    if (_userId > 0)
                    {
                        cmd.Parameters.Add("@UserId", SqlDbType.Int).Value = _userId;
                    }
                    if (_termId > 0)
                    {
                        cmd.Parameters.Add("@TermId", SqlDbType.Int).Value = _termId;
                    }
                    cmd.Parameters.Add("@FirstRecord", SqlDbType.Int).Value = _pageIndex;
                    cmd.Parameters.Add("@RecsPerPage", SqlDbType.Int).Value = _pageSize;
                    var output = new SqlParameter("@TotalRecord", SqlDbType.Int)
                    {
                        Direction = ParameterDirection.Output
                    };
                    cmd.Parameters.Add(output);
                    con.Open();
                    adapt.Fill(ds);
                    cmd.ExecuteNonQuery();
                    totalRecord = (int)output.Value;
                }
                var list = GetExaminationDtos(ds.Tables[0]);
                var result = new SearchResult<ExaminationDTO>(list, totalRecord);
                return result;
            }
        }

        private IList<ExaminationDTO> GetExaminationDtos(DataTable dtTable)
        {
            return (from DataRow dtRow in dtTable.Rows
                    select new ExaminationDTO
                    {
                        ExaminationId = (int)dtRow["ExaminationId"],
                        CreateDate = Convert.ToDateTime(dtRow["CreateDate"]).ToString("dd-MM-yyyy HH:mm:ss"),
                        TermName = dtRow["TermName"].ToString(),
                        Score = (int)dtRow["Score"],
                        UserId = (int)dtRow["UserId"],
                        ExamDate = Convert.ToDateTime(dtRow["CreateDate"]).ToString("yyyy-MM-dd")
                    }).ToList();
        }


    }
}
